Introduction

Library

# Load packages
library(here)         # relative filepaths for reproducibility
library(rio)          # read excel file from URL
library(tidyverse)    # data wrangling
library(stringi)      # string data wrangling
library(tidycensus)   # US census data
library(ggplot2)      # data visualization
library(kableExtra)   # table formatting
library(scales)       # palette and number formatting
library(unhcrthemes)  # data visualization themes
library(ggrepel)      # data visualization formatting to avoid overlapping
library(rcompanion)   # data visualization of variable distribution
library(ggpubr)       # data visualization of variable distribution
library(moments)      # measures of skewness and kurtosis
library(tinytable)    # format regression tables
library(modelsummary) # format regression tables

Load Functions

import::here( "fips_census_regions",
              "load_svi_data",
              "merge_svi_data",
              "census_division",
              "slopegraph_plot",
              "census_pull",
             # notice the use of here::here() that points to the .R file
             # where all these R objects are created
             .from = here::here("analysis/project_data_steps_knopp.R"),
             .character_only = TRUE)

# Load API key, assign to TidyCensus Package
source(here::here("analysis/password.R"))
census_api_key(census_api_key)

Data

# Load NMTC AND LIHTC data sets

svi_divisional_nmtc <- readRDS(here::here(paste0("data/wrangling/", str_replace_all(census_division, " ", "_"), "_svi_divisional_nmtc.rds")))

svi_national_nmtc <- readRDS(here::here(paste0("data/wrangling/", str_replace_all(census_division, " ", "_"), "_svi_national_nmtc.rds")))

svi_divisional_lihtc <- readRDS(here::here(paste0("data/wrangling/", str_replace_all(census_division, " ", "_"), "_svi_divisional_lihtc.rds")))

svi_national_lihtc <- readRDS(here::here(paste0("data/wrangling/", str_replace_all(census_division, " ", "_"), "_svi_national_lihtc.rds")))

svi_divisional_nmtc %>% head() %>% kbl() %>% kable_styling() %>% scroll_box(width = "100%")

svi_national_nmtc %>% head() %>% kbl() %>% kable_styling() %>% scroll_box(width = "100%")

svi_divisional_lihtc %>% head() %>% kbl() %>% kable_styling() %>% scroll_box(width = "100%")

svi_divisional_lihtc %>% head() %>% kbl() %>% kable_styling() %>% scroll_box(width = "100%")

hpi_df <- read.csv("https://r-class.github.io/paf-515-course-materials/data/raw/HPI/HPI_AT_BDL_tract.csv")

hpi_df_10_20 <- hpi_df %>% 
  mutate(GEOID10 = str_pad(tract, 11, "left", pad=0)) %>% 
  filter(year %in% c(2010, 2020))  %>%
 select(GEOID10, state_abbr, year, hpi) %>%
  pivot_wider(names_from = year, values_from = hpi) %>%
  mutate(housing_price_index10 = `2010`,
         housing_price_index20 = `2020`) %>%
  select(GEOID10, state_abbr, housing_price_index10, housing_price_index20)

# View data
hpi_df_10_20 %>% head() %>% kbl() %>% kable_styling() %>% scroll_box(width = "100%")

# Drop state_abbr column for joining
hpi_df_10_20 <- hpi_df_10_20 %>% select(-state_abbr)

msa_csa_crosswalk <- rio::import("https://r-class.github.io/paf-515-course-materials/data/raw/CSA_MSA_Crosswalk/qcew-county-msa-csa-crosswalk.xlsx", which=4)

msa_csa_crosswalk <- msa_csa_crosswalk %>% 
  mutate(county_fips = str_pad(`County Code`, 5, "left", pad=0),
         cbsa = coalesce(`CSA Title`, `MSA Title`),
         cbsa_code = coalesce(`CSA Code`, `MSA Code`),
         county_title = `County Title`)  %>% 
  select(county_fips, county_title, cbsa, cbsa_code)

msa_csa_crosswalk %>% head() %>% kbl() %>% kable_styling() %>% scroll_box(width = "100%")

Census Data

states <- list(svi_national_nmtc$state %>% unique())
states 

## [[1]]
##  [1] "AL" "AK" "AZ" "AR" "CA" "CO" "CT" "DE" "DC" "FL" "GA" "HI" "ID" "IL" "IN"
## [16] "IA" "KS" "KY" "LA" "ME" "MD" "MA" "MI" "MN" "MS" "MO" "MT" "NE" "NV" "NH"
## [31] "NJ" "NM" "NY" "NC" "ND" "OH" "OK" "OR" "PA" "RI" "SC" "SD" "TN" "TX" "UT"
## [46] "VT" "VA" "WA" "WV" "WI" "WY"

census_pull10 <- lapply(states, census_pull, yr = 2010)

census_pull10_df <- census_pull10[[1]] %>%  
  # Drop margin of error column
  select(-moe) %>%
  # Add suffix to variable names
  mutate(variable = paste0(variable, "_10")) %>%
  # Pivot data frame
  pivot_wider(
    names_from = variable,
    values_from = c(estimate)
  )

census_pull10_df %>% head(10) %>% kbl() %>% kable_styling() %>% scroll_box(width = "100%")

census_pull19 <- lapply(states, census_pull, yr = 2019)

census_pull19_df <- census_pull19[[1]] %>% 
  # Select columns
  select(GEOID, NAME, variable, estimate, moe) %>% 
  # Create individual FIPS columns for state, county, and tract
  mutate(FIPS_st = substr(GEOID, 1, 2),
         FIPS_county = substr(GEOID, 3, 5),
         FIPS_tract = substr(GEOID, 6, 11)) %>%
# Los Angeles, CA Census Tract fixes
                      mutate(FIPS_tract2 = if_else((FIPS_county == "037" & FIPS_st == "06" & FIPS_tract == "137000"), "930401", FIPS_tract )) %>%
# Pima County, AZ Census Tract fixes
                      mutate(FIPS_tract2 = if_else((FIPS_county == "019" & FIPS_st == "04" & FIPS_tract == "002704"), "002701", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "019" & FIPS_st == "04" & FIPS_tract == "002906"), "002903", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "019" & FIPS_st == "04" & FIPS_tract == "004118"), "410501", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "019" & FIPS_st == "04" & FIPS_tract == "004121"), "410502", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "019" & FIPS_st == "04" & FIPS_tract == "004125"), "410503", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "019" & FIPS_st == "04" & FIPS_tract == "005200"), "470400", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "019" & FIPS_st == "04" & FIPS_tract == "005300"), "470500", FIPS_tract2 )) %>%
# Madison County, NY Census Tract fixes
                      mutate(FIPS_tract2 = if_else((FIPS_county == "053" & FIPS_st == "36" & FIPS_tract == "030101"), "940101", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "053" & FIPS_st == "36" & FIPS_tract == "030102"), "940102", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "053" & FIPS_st == "36" & FIPS_tract == "030103"), "940103", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "053" & FIPS_st == "36" & FIPS_tract == "030200"), "940200", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "053" & FIPS_st == "36" & FIPS_tract == "030300"), "940300", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "053" & FIPS_st == "36" & FIPS_tract == "030401"), "940401", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "053" & FIPS_st == "36" & FIPS_tract == "030403"), "940403", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "053" & FIPS_st == "36" & FIPS_tract == "030600"), "940600", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "053" & FIPS_st == "36" & FIPS_tract == "030402"), "940700", FIPS_tract2 )) %>%
# Oneida County, NY Census Tract fixes
                      mutate(FIPS_tract2 = if_else((FIPS_county == "065" & FIPS_st == "36" & FIPS_tract == "024800"), "940000", FIPS_tract2 )) %>% 
                      mutate(FIPS_tract2 = if_else((FIPS_county == "065" & FIPS_st == "36" & FIPS_tract == "024700"), "940100", FIPS_tract2 )) %>%
                      mutate(FIPS_tract2 = if_else((FIPS_county == "065" & FIPS_st == "36" & FIPS_tract == "024900"), "940200", FIPS_tract2 )) %>%  
                      # Move columns in data set
                      relocate(c(FIPS_st, FIPS_county, FIPS_tract, FIPS_tract2),.after = GEOID) %>%
                      # Create new GEOID column
                      mutate(GEOID = paste0(FIPS_st, FIPS_county, FIPS_tract2)) %>% 
                      # Drop newly created FIPS columns and margin of error
                      select(-FIPS_st, -FIPS_county, -FIPS_tract, -FIPS_tract2, -moe) %>% 
                      # Add suffix
                      mutate(variable = paste0(variable, "_19")) %>%
                      # Pivot data set
                      pivot_wider(
                        names_from = variable,
                        values_from = c(estimate)
                      ) 

census_pull19_df %>% head(10) %>% kbl() %>% kable_styling() %>% scroll_box(width = "100%")

inflation_adj = 1.16

# Join 2010 and 2019 Median Income and Home Value Data
census_pull_df <- left_join(census_pull10_df, census_pull19_df[c("GEOID", "Median_Income_19", "Median_Home_Value_19")], join_by("GEOID" == "GEOID"))

# Create new inflation adjusted columns for 2010 median income and median home value, find changes over time
census_pull_df <- census_pull_df %>% 
                   mutate(Median_Income_10adj = Median_Income_10*inflation_adj,
                          Median_Home_Value_10adj = Median_Home_Value_10*inflation_adj,
                          Median_Income_Change = Median_Income_19 - Median_Income_10adj,
                          Median_Income_Change_pct = (Median_Income_19 - Median_Income_10adj)/Median_Income_10adj,
                          Median_Home_Value_Change = Median_Home_Value_19 - Median_Home_Value_10adj,
                          Median_Home_Value_Change_pct = (Median_Home_Value_19 - Median_Home_Value_10adj)/Median_Home_Value_10adj)

# View data
census_pull_df %>% head(10) %>% kbl() %>% kable_styling() %>% scroll_box(width = "100%")

NMTC Data Sets

Divisional

svi_divisional_nmtc_df0 <- left_join(svi_divisional_nmtc, census_pull_df, join_by("GEOID_2010_trt" == "GEOID"))

svi_divisional_nmtc_df1 <- left_join(svi_divisional_nmtc_df0, hpi_df_10_20, join_by("GEOID_2010_trt" == "GEOID10")) %>%
                          unite("county_fips", FIPS_st, FIPS_county, sep = "") 

svi_divisional_nmtc_df <- left_join(svi_divisional_nmtc_df1, msa_csa_crosswalk, join_by("county_fips" == "county_fips"))

svi_divisional_nmtc_df %>% head(10) %>% kbl() %>% kable_styling() %>% scroll_box(width = "100%")